﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace QT.SEO
{
    /// <summary>
    /// 网址操作类
    /// </summary>
    public class URL
    {
        /// <summary>
        /// 获取网址列表
        /// </summary>
        /// <returns>DataTalbe</returns>
        public static DataTable GetAll()
        {
            string sql = "select a.[url],a.[guid],b.[gname] from [QT_Data_UrlList] a left join [QT_Data_UrlGroup] b on a.[gid]=b.[guid] order by b.[gname],a.[url] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取指定分组的网址列表
        /// </summary>
        /// <param name="gid">分组ID</param>
        /// <returns>DataTalbe</returns>
        public static DataTable GetAll(string gid)
        {
            string sql = "select a.[url],a.[guid],a.[keywords],b.[gname] from [QT_Data_UrlList] a left join [QT_Data_UrlGroup] b on a.[gid]=b.[guid] where a.[gid]='" + gid+"' order by a.[url] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取指定分组的网址列表
        /// </summary>
        /// <param name="gids">一组分组ID</param>
        /// <returns>DataTalbe</returns>
        public static DataTable GetAll(List<string> gids)
        {
            string sql = "select a.[guid],a.[url],a.[gid],a.[keywords],b.[gname] from [QT_Data_UrlList] a left join [QT_Data_UrlGroup] b on a.[gid]=b.[guid] where a.[gid] in('" + string.Join("','", gids.ToArray()) + "')";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取URL信息
        /// </summary>
        /// <param name="guid">GUID</param>
        /// <returns>DataTalbe</returns>
        public static DataTable Get(string guid)
        {
            string sql = "select [url],[guid],[keywords],[gid] from [QT_Data_UrlList] where [guid]='" + guid + "'";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 添加网址,返回值-1表示失败；-2代表网址已存在
        /// </summary>
        /// <param name="url">网址</param>
        /// <param name="gid">分组ID</param>
        /// <returns>int</returns>
        public static int Add(string url,string gid)
        {
            if (IsExsit(gid, null, url)) return -2;
                        
            string sql = "insert into [QT_Data_UrlList]([guid],[url],[gid]) values('" + Guid.NewGuid().ToString().Replace("-", "") + "','" + url + "','" + gid + "')";
            int rval=new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
            
            return rval;
        }

        /// <summary>
        /// 添加网址,返回值-1表示失败；-2代表网址已存在
        /// </summary>
        /// <param name="guid">Guid</param>
        /// <param name="url">网址</param>
        /// <param name="gid">分组ID</param>
        /// <returns>int</returns>
        public static int Add(string guid,string url, string gid)
        {
            if (IsExsit(gid, null, url)) return -2;

            string sql = "insert into [QT_Data_UrlList]([guid],[url],[gid]) values('" + guid + "','" + url + "','" + gid + "')";
            int rval = new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);

            return rval;
        }

        /// <summary>
        /// 批量修改网址分组信息。
        /// </summary>
        /// <param name="guids">一组ID</param>
        /// <param name="gid">分组ID</param>
        /// <returns>int</returns>
        public static int update(List<string> guids, string gid)
        {
            string sql = "update [QT_Data_UrlList] set [gid]='" + gid + "' where [guid] in('" + string.Join("','", guids.ToArray()) + "')";
            return new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 修改网址。
        /// </summary>
        /// <param name="guid">ID</param>
        /// <param name="gid">分组ID</param>
        /// <param name="url">网址URL</param>
        /// <returns>int</returns>
        public static int update(string guid, string gid, string url)
        {
            if (IsExsit(gid, guid, url)) return -2;

            string sql = "update [QT_Data_UrlList] set [gid]='" + gid + "',[url]='" + url + "' where [guid]='" + guid + "'";
            return new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 清空URL列表(实际上是把各类数据迁移到历史表)
        /// </summary>
        /// <returns>int</returns>
        public static int Delete()
        {
            return Delete(null);
        }

        /// <summary>
        /// 删除指定分组的所有网址
        /// </summary>
        /// <param name="gid">分组ID</param>
        /// <returns>int</returns>
        public static int Delete(string gid)
        {
            string sql = "";

            sql = "insert into [QT_History_UrlList] select * from [QT_Data_UrlList]" + (string.IsNullOrEmpty(gid) ? "" : (" where [gid]='" + gid + "'"));
            new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);

            sql = "insert into [QT_History_SnapShot] select * from [QT_Data_SnapShot] where [uid] in(select [guid] from [QT_Data_UrlList]" + (string.IsNullOrEmpty(gid) ? "" : (" where [gid]='" + gid + "'")) + ")";
            new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);

            sql = "insert into [QT_History_KeywordRank] select * from [QT_Data_KeywordRank] where [uid] in(select [guid] from [QT_Data_UrlList]" + (string.IsNullOrEmpty(gid) ? "" : (" where [gid]='" + gid + "'")) + ")";
            new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);

            sql = "delete from [QT_Data_SnapShot] where [uid] in(select [guid] from [QT_Data_UrlList]" + (string.IsNullOrEmpty(gid) ? "" : (" where [gid]='" + gid + "'")) + ")";
            new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);

            sql = "delete from [QT_Data_KeywordRank] where [uid] in(select [guid] from [QT_Data_UrlList]" + (string.IsNullOrEmpty(gid) ? "" : (" where [gid]='" + gid + "'")) + ")";
            new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);

            sql = "delete from [QT_Data_UrlList]" + (string.IsNullOrEmpty(gid) ? "" : (" where [gid]='" + gid + "'"));
            int rval = new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);

            return rval;
        }

        /// <summary>
        /// 删除给定的URL地址
        /// </summary>
        /// <param name="gid">分组ID</param>
        /// <param name="guid">网址ID</param>
        /// <returns>int</returns>
        public static int Delete(string gid, string guid)
        {
            List<string> guids = new List<string>();
            guids.Add(guid);

            return Delete(gid, guids);
        }

        /// <summary>
        /// 删除给定的一组URL地址
        /// </summary>
        /// <param name="gid">分组ID</param>
        /// <param name="guids">一组网址ID</param>
        /// <returns>int</returns>
        public static int Delete(string gid, List<string> guids)
        {
            string sql = "";

            sql = "insert into [QT_History_UrlList] select * from [QT_Data_UrlList] where [guid] in('" + string.Join("','", guids.ToArray()) + "')" + (string.IsNullOrEmpty(gid) ? "" : (" and [gid]='" + gid + "'"));
            new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);

            sql = "insert into [QT_History_SnapShot] select * from [QT_Data_SnapShot] where [uid] in('" + string.Join("','", guids.ToArray()) + "')" + (string.IsNullOrEmpty(gid) ? "" : (" and [gid]='" + gid + "'"));
            new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);

            sql = "insert into [QT_History_KeywordRank] select * from [QT_Data_KeywordRank] where [uid] in('" + string.Join("','", guids.ToArray()) + "')" + (string.IsNullOrEmpty(gid) ? "" : (" and [gid]='" + gid + "'"));
            new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);

            sql = "delete from [QT_Data_SnapShot] where [uid] in('" + string.Join("','", guids.ToArray()) + "')" + (string.IsNullOrEmpty(gid) ? "" : (" and [gid]='" + gid + "'"));
            new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);

            sql = "delete from [QT_Data_KeywordRank] where [uid] in('" + string.Join("','", guids.ToArray()) + "')" + (string.IsNullOrEmpty(gid) ? "" : (" and [gid]='" + gid + "'"));
            new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);

            sql = "delete from [QT_Data_UrlList] where [guid] in('" + string.Join("','", guids.ToArray()) + "')" + (string.IsNullOrEmpty(gid) ? "" : (" and [gid]='" + gid + "'"));
            int rval = new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);

            return rval;
        }

        /// <summary>
        /// 检查网址是否已存在，存在返回true；不存在返回false。
        /// </summary>
        /// <param name="gid">分组ID</param>
        /// <param name="guid">ID</param>
        /// <param name="url">网址</param>
        /// <returns>bool</returns>
        private static bool IsExsit(string gid, string guid, string url)
        {
            string sql = "select guid from [QT_Data_UrlList] where [gid]='" + gid + "' and [url]='" + url + "'" + (guid == null ? "" : (" and [guid]<>'" + guid + "'"));
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
            if (dt.Rows.Count == 0) return false;
            return true;
        }
    }

    /// <summary>
    /// 网址分组操作类
    /// </summary>
    public class Group
    {
        /// <summary>
        /// 获取网址分组列表
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable GetAll()
        {
            string sql = "select [guid],[gname] from [QT_Data_UrlGroup] order by [guid] asc";

            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);

            return dt;
        }
        
        /// <summary>
        /// 获取分组ID
        /// </summary>
        /// <param name="gname">分组名称</param>
        /// <returns>string</returns>
        public static string Get(string gname)
        {
            string sql = "select [guid],[gname] from [QT_Data_UrlGroup] where [gname]='" + gname + "'";

            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);

            return dt.Rows[0][0].ToString();
        }

        /// <summary>
        /// 获取分组ID
        /// </summary>
        /// <param name="gnames">一组分组名称</param>
        /// <returns>DataTable</returns>
        public static DataTable Get(List<string> gnames)
        {
            string sql = "select [guid],[gname] from [QT_Data_UrlGroup] where [gname] in('" + string.Join("','", gnames.ToArray()) + "')";

            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);

            return dt;
        }

        /// <summary>
        /// 添加网址分组,-2为已存在；1为成功
        /// </summary>
        /// <param name="gname">分组名称</param>
        /// <returns>int</returns>
        public static int Add(string gname)
        {
            if (IsExsit(null,gname)) return -2;

            string sql = "insert into [QT_Data_UrlGroup]([guid],[gname]) values('" + Guid.NewGuid().ToString().Replace("-", "") + "','" + gname + "')";

            return new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 修改网址分组,-2为已存在；1为成功
        /// </summary>
        /// <param name="guid">分组ID</param>
        /// <param name="gname">分组名称</param>
        /// <returns>int</returns>
        public static int Update(string guid, string gname)
        {
            if (IsExsit(guid, gname)) return -2;

            string sql = "update [QT_Data_UrlGroup] set [gname]='" + gname + "' where [guid]='" + guid + "'";

            return new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
        }
        
        /// <summary>
        /// 删除分组
        /// </summary>
        /// <param name="guid">分组ID</param>
        /// <returns></returns>
        public static int Delete(string guid)
        {
            List<string> guids = new List<string>();
            guids.Add(guid);
            return Delete(guids);
        }

        /// <summary>
        /// 批量删除分组
        /// </summary>
        /// <param name="guids">一组分组ID</param>
        /// <returns>int</returns>
        public static int Delete(List<string> guids)
        {
            string sql = "";
            int rval=0;
            sql = "insert into [QT_History_UrlGroup] select * from [QT_Data_UrlGroup] where [guid] in('" + string.Join("','", guids.ToArray()) + "')";
            rval = new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
            if (rval <= 0) return rval;

            sql = "delete from [QT_Data_UrlGroup] where [guid] in('" + string.Join("','", guids.ToArray()) + "')";
            rval = new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);

            return rval;
        }

        /// <summary>
        /// 
        /// 判断分组ID是否存在
        /// </summary>
        /// <param name="guid">分组ID</param>
        /// <param name="gname">分组名称</param>
        /// <returns>bool</returns>
        public static bool IsExsit(string guid, string gname)
        {
            string sql = "select [guid] from [QT_Data_UrlGroup] where [gname]='" + gname + "'" + (guid == null ? "" : (" and [guid]<>'" + guid + "'"));

            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
            if (dt.Rows.Count > 0) return true;
            else return false;
        }
    }
}
